package cn.com.connext.scrm.repository.master;

import cn.com.connext.scrm.domain.master.PrizeGood;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Date;
import java.util.List;

/**
 * 2 * @Author: shawn
 * 3 * @Date: 2020/8/11 4:17 下午
 * 4
 */
@Repository
public interface PrizeGoodRepository extends JpaRepository<PrizeGood, Integer>, JpaSpecificationExecutor {

    @Query(value = "select * from prize_good where is_enable = true and stock > 0 and start_time <= now() and end_time >= now()", nativeQuery = true)
    List<PrizeGood> getEnableIdsAndStockIsNotNull();


    @Query(value = "select * from prize_good where activity_id=?1 and is_enable = true and stock > 0 and start_time <= now() and end_time >= now()", nativeQuery = true)
    List<PrizeGood> getEnableIdsAndStockIsNotNullByActivityId(String activityId);

    @Query(value = "select good_name from prize_good where good_id = ?1 limit 1", nativeQuery = true)
    String getName(Integer goodId);

    PrizeGood findFirstByGoodId(Integer goodId);

    @Query(value = "select * from prize_good where good_id = ?1 and start_time <= now() and end_time >= now() limit 1", nativeQuery = true)
    PrizeGood findByGoodIdAndDate(Integer goodId);

    List<PrizeGood> findByGoodIdAndStartTimeBeforeAndEndTimeAfter(Integer goodId, Date startTime, Date endTime);

    @Query(value = "select * from prize_good where good_id = ?1 and activity_id=?2 and enable_flag=1 and start_time <= ?3 and end_time >= ?4 limit 1", nativeQuery = true)
    PrizeGood findByGoodIdAndActivityIdAndStartTimeBeforeAndEndTimeAfter(Integer goodId, String activityId, Date startTime, Date endTime);

    PrizeGood findByIdAndActivityId(Integer id, String activityId);

    @Query(value = "select * from prize_good where type = ?1 and activity_id=?2 and is_enable=1 and start_time <= now() and end_time >= now()", nativeQuery = true)
    List<PrizeGood> findAllByActivityIdAndType(Integer type, String activityId);

    List<PrizeGood> findByActivityId(String activityId);
    @Query(value = "select * from prize_good where  activity_id = :activityId and is_enable=1  and end_time >= now() order by end_time", nativeQuery = true)
    List<PrizeGood> findDwaByActivityId(@Param("activityId") String activityId);

    @Query(value = "select * from prize_good where  activity_id = :activityId  and id in (:goodIds) and is_enable=1  and end_time < now() order by end_time", nativeQuery = true)
    List<PrizeGood> findExpiredDwaByActivityId(@Param("activityId") String activityId, @Param("goodIds")List<Integer> goodIds);
    @Query(value = "select * from prize_good where  activity_id = :activityId and id in (:goodIds) and is_enable=1  and end_time >= now() order by end_time", nativeQuery = true)
    List<PrizeGood> findDwaByActivityIdAndGoodIds(@Param("activityId")String activityId, @Param("goodIds")List<Integer> goodIds);
    @Query(value = "select * from prize_good where  id = :goodId and is_enable=1  and start_time <= now() and end_time >= now()", nativeQuery = true)
    PrizeGood findCanUse(@Param("goodId")Integer goodId);
    ///根据奖品名称模糊查询所有对应的奖品id
    @Query(value = "select m.id as prizeGoodId from PrizeGood m where m.goodName like %?1% order by m.id desc")
    List<Integer> findAllPrizeGoodNameLikeThis(@Param("goodName")String goodName);
    @Query(value = "select * from prize_good where  id in :goodIds and type in:types", nativeQuery = true)
    List<PrizeGood> findByIds(@Param("goodIds")List<Integer> goodIds,@Param("types")List<Integer> types);

    @Query(value = "select * from prize_good where activity_id=?1 and is_enable = true and start_time <= now() and end_time >= now()", nativeQuery = true)
    List<PrizeGood> getEnableIdsByActivityId(String activityId);

    @Query(value = "select sum(probability) from prize_good where activity_id = :activityId and id <> :id", nativeQuery = true)
    Double findProbabilityByActivityIdAndId(@Param("activityId")String activityId,@Param("id")Integer id);

    @Query(value = "select sum(probability) from prize_good where activity_id = :activityId", nativeQuery = true)
    Double findProbabilityByActivityId(@Param("activityId")String activityId);

    @Query(value = "select * from prize_good where good_code = :goodCode", nativeQuery = true)
    List<PrizeGood> findByGoodCode(@Param("goodCode") String goodCode);

    @Query(value = "select * from prize_good where good_code = ?and id != ?", nativeQuery = true)
    List<PrizeGood> findByGoodCodeAndId(String goodCode,  Integer id);

    @Transactional
    @Modifying
    @Query(value = "update prize_good set stock =:stock where stock =:stockOld and id =:id ", nativeQuery = true)
    Integer updateStock(@Param("stock") Integer stock, @Param("stockOld") Integer stockOld,@Param("id") Integer id);
}
